from io import BytesIO

import openpyxl as xl
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.styles import Side, Border, colors

# a_title_align = Alignment(horizontal='centerContinuous', vertical='center', text_rotation=0, wrap_text=True,
#                           shrink_to_fit=True, indent=0)
# a_title_font = Font(size=24, bold=True, name='宋体', color="000000")

# b级标题
b_title_align = Alignment(horizontal='centerContinuous', vertical='center', text_rotation=0, wrap_text=True,
                          shrink_to_fit=True, indent=0)
# b_title_align = Alignment(horizontal='centerContinuous', vertical='center', text_rotation=0, wrap_text=True,
#                           shrink_to_fit=True, indent=0)
b_title_font = Font(size=11, bold=False, name='宋体', color="000000")
b_title_fill = PatternFill(fill_type="solid", start_color='FFfdbf01', end_color='FFfdbf01')

# #5CA1D7，#9DD6F4，#9DD6F4，#FF9F64，#725746
tag_title_fill_1 = PatternFill(fill_type='solid', start_color='f8c7dc', end_color='f8c7dc')
tag_title_fill_2 = PatternFill(fill_type='solid', start_color='fffdd7', end_color='fffdd7')
tag_title_fill_3 = PatternFill(fill_type='solid', start_color='c5e4d4', end_color='c5e4d4')
tag_title_fill_4 = PatternFill(fill_type='solid', start_color='D6CFCB', end_color='D6CFCB')
tag_title_fill_5 = PatternFill(fill_type='solid', start_color='c7cde7', end_color='c7cde7')
tag_title_fill_6 = PatternFill(fill_type='solid', start_color='fae0bf', end_color='fae0bf')
tihs_tag_fill_list = [tag_title_fill_1, tag_title_fill_2, tag_title_fill_3, tag_title_fill_4,
                      tag_title_fill_5, tag_title_fill_6]


class OCROutBase:
    def __init__(self):
        self.wb = None
        self.ws = None

    def new_sheet(self, title='sheet'):
        self.wb = getattr(self, 'wb', None)
        if not self.wb:
            self.wb = xl.Workbook()
            self.ws = self.wb.active
            self.ws.title = title
        else:
            self.ws = self.wb.create_sheet("new_sheet")
            self.ws.title = title

    def get_file_name(self, file_name):
        if not file_name.endswith('.xlsx'):
            self.file_name = file_name + ".xlsx"
        else:
            self.file_name = file_name


class KenYanToXlsx3(OCROutBase):
    #
    def __init__(self):
        # Style.__init__(self)
        super(KenYanToXlsx3, self).__init__()
        self.ocr_template = ''
        self.max_num = 1  # 一对多插入,最大占用行数
        self.now_y = 5  # 目前执行行
        self.num_1 = 9
        self.sq_num = 10  # 截止到其他诊断
        self.qfs_base_info_json = {}
        self.qfs_jybfd_info_json = {}
        self.en_ch_name = {}
        self.ch_en_name = {}
        side = Side(border_style='thin', color=colors.BLACK)
        self.border = Border(top=side, bottom=side, left=side, right=side)
        self.default_font = Font(size=11, bold=False, name='宋体', color="000000")
        self.default_align = Alignment(horizontal='centerContinuous', vertical='center', text_rotation=0,
                                       wrap_text=True,
                                       shrink_to_fit=True, indent=0)
        self.default_align2 = Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=True,
                                        shrink_to_fit=True, indent=0)
        self.default_align3 = Alignment(horizontal='left', vertical='center', text_rotation=0, wrap_text=True,
                                        shrink_to_fit=True, indent=0)

    def set_default_style(self, x, y, context_len, header_num):

        if x <= 3 or y <= header_num:
            xy = self.ws.cell(y, x)
            xy.font = self.default_font
            if y <= header_num:
                xy.alignment = self.default_align
            elif context_len >= 20:
                xy.alignment = self.default_align3
            else:
                xy.alignment = self.default_align2
            xy.border = self.border
        else:
            xy = self.ws.cell(y, x)
            xy.alignment = self.default_align2

    def init_sheet(self, data_style_json):
        max_col = len(data_style_json['data'][0]) if data_style_json else 1
        header_num = data_style_json.get('header_num') or 1
        freeze_row = data_style_json.get('freeze_row') or 0
        freeze = data_style_json.get('freeze') or (0, 0)
        # 行高
        for y in data_style_json['row_dimensions']:
            for y_i in y['y']:
                self.ws.row_dimensions[y_i].height = y['height']
        #
        tihs_tag_fill_index = 0
        for y, context_list in enumerate(data_style_json['data'], 1):
            for x, context in enumerate(context_list, 1):
                context = context if context is not None else ''

                if context is None or context in ['None']:
                    context = ''
                self.ws.cell(y, x, context)
                self.set_default_style(x, y, len(str(context)), header_num)
                if y <= header_num:
                    self.ws.cell(y, x).fill = tihs_tag_fill_list[tihs_tag_fill_index]
                    tihs_tag_fill_index = tihs_tag_fill_index + 1 if tihs_tag_fill_index < len(tihs_tag_fill_list) - 1 \
                        else 0

        tihs_tag_fill_index = 1

        for i in data_style_json['merge']:
            self.ws.merge_cells(**i)
            if i['start_row'] <= header_num:
                self.ws.cell(i['start_row'], i['start_column']).fill = tihs_tag_fill_list[tihs_tag_fill_index]
                tihs_tag_fill_index = tihs_tag_fill_index + 1 if tihs_tag_fill_index < len(tihs_tag_fill_list) - 1 \
                    else 0

        # 冻结窗格
        if freeze and freeze[0] > 1 or freeze[1] > 1:
            cloumn_num = xl.utils.get_column_letter(freeze[0])
            row_num = freeze[1]
            print(f"{cloumn_num}{row_num}")
            self.ws.freeze_panes = f"{cloumn_num}{row_num}"
        #
        # self.ws.column_dimensions['A'].width = 19
        # self.ws.column_dimensions['B'].width = 10
        # self.ws.column_dimensions['C'].width = 22

    def row_add_data(self, data_style_json, new_list):
        for data in new_list:
            data_style_json['data'].append(data)

    def row_case_add_data(self, title, data_style_json):
        self.new_sheet(title=title)

        self.init_sheet(data_style_json)

    def row_fun(self, data_style_json):
        print(f'---row_fun---')
        for k, v in data_style_json.items():
            self.row_case_add_data(k, v)
        # ----------------
        if self.wb is None:
            self.new_sheet(title="没有查到内容")
        output = BytesIO()

        self.wb.save(output)  # 将Excel文件内容保存到IO中
        output.seek(0)  # 重新定位到开始
        file_size = len(output.getvalue())
        output.seek(0)
        with open('1.xlsx', 'wb') as f:
            f.write(output.getvalue())
        return output, file_size


if __name__ == '__main__':
    data_style_json = {
        "sheetA": {
            "merge": [  # 合并单元格
                {"start_column": 2, "end_column": 3, "start_row": 1, "end_row": 1},
                {"start_column": 4, "end_column": 5, "start_row": 1, "end_row": 1}
            ],
            "row_dimensions": [  # 行高
                {"y": list(range(1, 60)), "height": 30},
            ],
            "column_dimensions": [  # 列宽
                {"x": ['A'], "width": 20},
                {"x": ['B'], "width": 30},
            ],
            "data": [
                ['学科', '21年级', '', '22年级', '', '23年级'],
                ['', '21级1班', '21级2班', '22级1班', '22级2班', '23级1班', ],
                ['化学', '1', '2', '3', '4', '5'],
                ['语文', '1', '2', '3', '4', '5'],
            ],
            'header_num': 1,  # 表头行数
            'freeze': (1, 2),  # 冻结窗格
        }
    }
    excel_obj = KenYanToXlsx3()
    excel_obj.row_fun(data_style_json)
